<?php
if(!defined('FROM_INDEX'))exit('Invalid request');

function get_user_sql()
{
	$pre = TABLE_PREFIX;
	
	return <<<SQL
select
	u.*, g.name as group_name, g.privilege
from
	`{$pre}user` u
	inner join `{$pre}user_group` g on g.`id` = u.`group_id`
where
	u.`name` = ?
	or u.`id` = ?
SQL;
}

function get_user_group_list_sql()
{
	$pre = TABLE_PREFIX;
	
	return <<<SQL
select
	*
from
	{$pre}user_group	
SQL;
}

function add_user_sql()
{
	$pre = TABLE_PREFIX;
	
	return <<<SQL
insert into `{$pre}user`
	(`name`, `nick_name`, `email`, `password`, 
	`phone_number`, `group_id`, `is_admin`, `register_time`, 
	`register_ip`, `last_ip`, `last_activity`)
values
	(?,?,?,?,
	?,?,?,?,
	?,?,?)
SQL;
}

function delete_user_sql($ids)
{
	$pre = TABLE_PREFIX;
	
	return <<<SQL
delete from `{$pre}user`
where
	`id` in ($ids)
	and `id` != '1'
SQL;
}

function get_movie_category_list_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`, `name`, `order`
from
	`{$pre}movie_category`
order by `order` asc
SQL;
}

function add_movie_category_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
insert into `{$pre}movie_category`
	(`name`, `order`)
values
	(?,?)
SQL;
}

function update_movie_category_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie_category`
set
	`name` = ?, 
	`order` = ?
where
	`id` = ?
SQL;
}

function get_movie_list_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`, `name`, `name_en`, `director`, 
	`release_date`, `star_level`, `category_id`, `add_time`, 
	`update_time`
from
	`{$pre}movie` m
SQL;
}

function get_movie_info_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	m.*,
	md.desc
from
	`{$pre}movie` m
	left join `{$pre}movie_desc` md on md.movie_id = m.id
where
	m.`id` = ?
	and m.is_delete = '0'
SQL;
}

function check_movie_exists_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	id
from
	`{$pre}movie`
where
	`name` = ?
	and `id` <> ?
SQL;
}

function get_movie_desc_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`movie_id`, `desc`, `add_time`, `update_time`
from
	`{$pre}movie_desc`
where
	`movie_id` = ?
SQL;
}

function get_movie_actor_list_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`, `movie_id`, `actor_name`, `actor_name_en`, `gender`, `is_main`
from
	`{$pre}movie_actor`
where
	`movie_id` = ?
SQL;
}

function add_movie_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
insert into `{$pre}movie`
	(`name`, `name_en`, `director`, `release_date`, 
	`star_level`, `category_id`, `add_time`, `update_time`, 
	`is_hot`, `is_expect`, `is_delete`)
values
	(?,?,?,?,
	?,?,?,?,
	?,?,'0')
SQL;
}

function add_movie_actors_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
insert into `{$pre}movie_actor`
	(`movie_id`, `actor_name`, `actor_name_en`, `gender`, `is_main`) 
values
	(?,?,?,?,?)
SQL;
}

function delete_movie_actors_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
delete from
	`{$pre}movie_actor` 
where
	`movie_id` = ?
SQL;
}

function add_movie_desc_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
insert into `{$pre}movie_desc`
	(`movie_id`, `desc`, `add_time`, `update_time`)
values
	(?,?,?,?)
SQL;
}

function update_movie_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie`
set
	`name` = ?, 
	`name_en` = ?, 
	`director` = ?, 
	`release_date` = ?, 
	`star_level` = ?, 
	`category_id` = ?, 
	`update_time` = ?, 
	`is_hot` = ?, 
	`is_expect` = ?
where
	`id` = ?
SQL;
}

function update_movie_desc_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie_desc`
set
	`desc` = ?,
	`update_time` = ?
where
	`movie_id` = ?
SQL;
}

function delete_movie_sql($ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
delete from `{$pre}movie`
where
	`id` in ($ids)
	and `is_delete` = '1'
SQL;
}

function delete_movie_to_trash_sql($ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie`
set
	`is_delete` = '1',
	update_time = ?
where
	`id` in ($ids)
SQL;
}

function delete_movie_desc_sql($ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
delete from `{$pre}movie_desc`
where
	`movie_id` in ($ids)
SQL;
}

function get_trash_movie_ids_sql($ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`
from
	`{$pre}movie`
where
	`id` in ($ids)
	and is_delete = '1'
SQL;
}

function get_movie_image_ids_sql($movie_ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`
from
	`{$pre}movie_image`
where
	`movie_id` in ($movie_ids)
SQL;
}

function get_movie_image_list_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`, `movie_id`, `name`, `image_url`, 
	`desc`, `size`, `width`, `height`, 
	`category_id`, `add_time`
from
	`{$pre}movie_image`
where
	`movie_id` = ?
	and `category_id` = ?
SQL;
}

function get_movie_refer_list_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`, `movie_id`, `refer`, `add_time`, `update_time`
from
	`{$pre}movie_refer`
SQL;
}

function get_movie_titbit_list_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`, `movie_id`, `titbit_url`, `add_time`
from
	`{$pre}movie_titbit`
SQL;
}

function update_movie_status_sql($field, $ids, $status)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie`
set
	`$field` = '$status'
where
	`id` in ($ids)
SQL;
}

function get_movie_review_list_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`, `movie_id`, `title`, `author`, 
	`short_desc`, `reply_count`, `recommend_count`, `up_count`, 
	`down_count`, `star_level`, `add_time`, `update_time`
from
	`{$pre}movie_review`
SQL;
}

function get_movie_review_detail_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	mr.id, mr.movie_id, mr.title, mr.author,
	mr.reply_count, mr.recommend_count, mr.up_count, mr.down_count, 
	mr.star_level, mr.add_time, mr.update_time, mrd.content
from
	`{$pre}movie_review` mr
	inner join `{$pre}movie_review_desc` mrd on mr.id = mrd.	movie_review_id
where
	mr.id = ?
	and mr.is_delete = '0'
SQL;
}

function get_movie_review_reply_list_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`, `movie_review_id`, `user_id`, `user_address`, 
	`content`, `up_count`, `down_count`, `add_time`, 
	`update_time`
from
	`{$pre}movie_review_reply`
SQL;
}

function add_movie_review_reply_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
insert into `{$pre}movie_review_reply`
	(`movie_id`, `movie_review_id`, `user_id`, `user_address`, 
	`content`, `up_count`, `down_count`, `add_time`, 
	`update_time`)
values
	(?,?,?,?,
	?,?,?,?,
	?)
SQL;
}

function add_movie_review_reply_count_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie_review`
	set reply_count = reply_count + 1
where
	`id` = ?
SQL;
}

function add_movie_review_recommend_count_sql($set)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie_review`
	set recommend_count = recommend_count + 1
where
	`id` = ? 
SQL;
}

function get_movie_review_reply_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	*
from
	`{$pre}movie_review_reply`
where
	`id` = ? 
SQL;
}

function add_movie_review_reply_vote_sql($set)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie_review_reply`
	set $set
where
	`id` = ? 
SQL;
}

function check_movie_review_exists_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`
from
	`{$pre}movie_review`
where
	`title` = ?
	and `movie_id` = ?
	and `id` <> ?
SQL;
}

function add_movie_review_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
insert into `{$pre}movie_review`
	(`movie_id`, `title`, `author`, `short_desc`, 
	`reply_count`, `recommend_count`, `up_count`, `down_count`, 
	`star_level`, `add_time`, `update_time`)
values
	(?,?,?,?,
	?,?,?,?,
	?,?,?)
SQL;
}

function update_movie_review_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update
	`{$pre}movie_review`
set
	`title` = ?,
	`author` = ?,
	`star_level` = ?,
	`short_desc` = ?, 
	`update_time` = ?
where
	`id` = ?
SQL;
}

function get_movie_review_info_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	mr.*,
	mrd.content
from
	`{$pre}movie_review` mr
	left join `{$pre}movie_review_desc` mrd on mrd.movie_review_id = mr.id
where
	mr.id = ?
	and mr.is_delete = '0'
SQL;
}

function add_movie_review_desc_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
insert into `{$pre}movie_review_desc`
	(`movie_review_id`, `content`, `add_time`, `update_time`)
values
	(?,?,?,?)
SQL;
}

function update_movie_review_desc_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update
	`{$pre}movie_review_desc`
set
	`content` = ?,
	`update_time` = ?
where
	movie_review_id = ?
SQL;
}

function delete_movie_review_to_trash_sql($ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update
	`{$pre}movie_review`
set
	`is_delete` = '1',
	`update_time` = ?
where
	`id` in ($ids)
	and is_delete = '0'
SQL;
}

function recover_movie_review_sql($ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update
	`{$pre}movie_review`
set
	`is_delete` = '0',
	`update_time` = ?
where
	`id` in ($ids)
	and is_delete = '1'
SQL;
}

function delete_movie_review_sql($ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
delete from
	`{$pre}movie_review`
where
	`id` in ($ids)
	and is_delete = '1'
SQL;
}

function delete_movie_review_desc_sql($review_ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
delete from
	`{$pre}movie_review_desc`
where
	`movie_review_id` in ($review_ids)
SQL;
}

function get_movie_review_ids_sql($movie_ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	mr.`id`
from
	`{$pre}movie_review` mr
	inner join `{$pre}movie` m on mr.movie_id = m.id
where
	m.`id` in ($movie_ids)
SQL;
}

function get_trash_review_ids_sql($review_ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	mr.`id`
from
	`{$pre}movie_review` mr
where
	mr.`id` in ($review_ids)
	and mr.is_delete = '1'
SQL;
}

function get_trash_review_reply_ids_sql($review_ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	mrr.`id`
from
	`{$pre}movie_review_reply` mrr
	inner join `{$pre}movie_review` mr on mrr.movie_review_id = mr.id
where
	mr.`id` in ($review_ids)
	and mr.is_delete = '1'
SQL;
}

function get_movie_review_reply_info_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	`id`, `movie_id`, `movie_review_id`, `user_id`, 
	`user_address`, `content`, `up_count`, `down_count`, 
	`add_time`, `update_time`
from
	`{$pre}movie_review_reply`
where
	`id` = ?
SQL;
}

function update_movie_review_reply_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie_review_reply`
set
	`content` = ?,
	`update_time` = ?
where
	`id` = ?
SQL;
}

function get_review_ids_from_reply($reply_ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	distinct
	movie_review_id
from
	`{$pre}movie_review_reply`
where
	`id` in ($reply_ids)
SQL;
}

function reset_movie_review_reply_count_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie_review`
set
	`reply_count` = (select count(1) from `{$pre}movie_review_reply` where movie_review_id = ?),
	`update_time` = ?
where
	`id` = ?
SQL;
}

function delete_movie_review_reply_sql($ids)
{
	$pre = TABLE_PREFIX;
	return <<<SQL
delete from `{$pre}movie_review_reply`
where
	`id` in ($ids)
SQL;
}

function add_movie_image_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
insert into `{$pre}movie_image`
	(`movie_id`, `name`, `image_url`, `desc`, 
	`size`, `width`, `height`, `category_id`, 
	`add_time`)
values
	(?,?,?,?,
	?,?,?,?,
	?)
SQL;
}

function delete_movie_image_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
delete from `{$pre}movie_image`
where
	
SQL;
}

function get_movie_refer_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	*
from
	`{$pre}movie_refer`
where
	`id` = ?
SQL;
}

function add_movie_refer_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
insert into `{$pre}movie_refer`
	(`movie_id`, `refer`, `add_time`, `update_time`)
values
	(?,?,?,?)
SQL;
}

function update_movie_refer_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie_refer`
set
	`movie_id` = ?, 
	`refer` = ?, 
	`update_time` = ?
where
	`id` = ?
SQL;
}

function delete_movie_refer_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
delete from `{$pre}movie_refer`
where
	
SQL;
}

function get_movie_titbit_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
select
	*
from
	`{$pre}movie_titbit`
where
	`id` = ?
SQL;
}

function add_movie_titbit_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
insert into `{$pre}movie_titbit`
	(`movie_id`, `titbit_url`, `add_time`)
values
	(?,?,?)
SQL;
}

function update_movie_titbit_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
update `{$pre}movie_titbit`
set
	`movie_id` = ?, 
	`titbit_url` = ?
where
	`id` = ?
SQL;
}

function delete_movie_titbit_sql()
{
	$pre = TABLE_PREFIX;
	return <<<SQL
delete from `{$pre}movie_titbit`
where
	
SQL;
}